from flask import Flask
from migrate import Migrate
from venv.include.config import Config
from datetime import datetime
from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String, Boolean, DateTime, ForeignKey, create_engine)
from sqlalchemy import PrimaryKeyConstraint, UniqueConstraint, CheckConstraint, Index
from sqlalchemy import ForeignKey
from sqlalchemy import insert
from sqlalchemy.sql import select

# metadata = MetaData()
# cookies = Table('cookies', metadata,
#                 Column('cookie_id', Integer(), primary_key=True),
#                 Column('cookie_name', String(50), index=True),
#                 Column('cookie_recipe_url', String(255)),
#                 Column('cookie_sku', String(55)),
#                 Column('quantity', Integer()),
#                 Column('unit_cost', Numeric(12, 2))
#                 )
# users = Table('users', metadata,
#               Column('user_id', Integer(), primary_key=True),
#               Column('customer_number', Integer(), autoincrement=True),
#               Column('username', String(15), nullable=False, unique=True),
#               Column('email_address', String(255), nullable=False),
#               Column('phone', String(20), nullable=False),
#               Column('password', String(25), nullable=False),
#               Column('created_on', DateTime(), default=datetime.now),
#               Column('updated_on', DateTime(),
#                      default=datetime.now, onupdate=datetime.now)
#               )
# orders = Table('orders', metadata,
#                Column('order_id', Integer(), primary_key=True),
#                Column('user_id', ForeignKey('users.user_id')),
#                Column('shipped', Boolean(), default=False)
#                )
# line_items = Table('line_items', metadata,
#                    Column('line_items_id', Integer(), primary_key=True),
#                    Column('order_id', ForeignKey('orders.order_id')),
#                    Column('cookie_id', ForeignKey('cookies.cookie_id')),
#                    Column('quantity', Integer()),
#                    Column('extended_cost', Numeric(12, 2))
#                    )
#
# engine = create_engine("sqlite:///test.db")
# metadata.create_all(engine)
# connection = engine.connect()

app = Flask(__name__)
app.config.from_object(Config)












#创建引擎
engine = create_engine("sqlite:///test2.db")
#连接数据库
connection = engine.connect()
#元数据
metadata = MetaData()

cookies = Table("cookies", metadata,
                Column("cookie_id", Integer(), primary_key=True),
                Column("cookie_name", String(50), index=True),
                Column("cookie_url", String(50), ),
                Column("quantity", Integer(), ),
                Column("unit_cost", Numeric(12))
                )

list(cookies.columns)

# users = Table("users", metadata,
#               Column("user_id", Integer(), primary_key=True),
#               Column("user_name", String(20), nullable=False, unique=True),
#               Column("email_address", String(), nullable=False, unique=True),
#               Column("phone", String(20), nullable=False),
#               Column("password", String(25), nullable=False),
#               Column("created_on", DateTime(), default=datetime.now),
#               Column("updated_on", DateTime(), default=datetime.now, onupdate=datetime.now)
#               )
# list(users.columns)

# PrimaryKeyConstraint("user_id", name="user_pk")
# UniqueConstraint("user_name", name="uix_username")
# CheckConstraint("unit_cost >= 0.00", name='unit_cost_positive')
# Index("index_cookies_cookie_name", cookies.c.cookie_name)
# Index("index_test", cookies.c.cookie_id, cookies.c.cookie_name)//复合索引

orders = Table("orders", metadata,
               Column("order_id", Integer(), primary_key=True),
               Column("user_id", ForeignKey("users.user_id")),
               Column("shipped", Boolean(), default=False)
               )
line_items = Table("line_items", metadata,
                   Column("line_items_id", Integer(), primary_key=True),
                   Column("order_id", ForeignKey("orders.order_id")),
                   Column("cookie_id", ForeignKey("cookies.cookie_id")),
                   Column("quantity", Integer()),
                   Column("extended_cost", Numeric(12, 2))
                   )

metadata.create_all(engine)

# ins = cookies.insert().values(
#     cookie_id="1",
#     cookie_name="chocolate chip",
#     cookie_url="http://some.aweso.me/cookie/recipe.html",
#     quantity="1",
#     unit_cost="0.15"
# )
# print(str(ins))
#
# result = connection.execute(ins)
#
# print(result.inserted_primary_key)
#
# ins2 = insert(cookies).values(
#     cookie_id="2",
#     cookie_name="chocolate chip2",
#     cookie_url="http://some.aweso.me/cookie/recipe.html",
#     quantity="2",
#     unit_cost="0.25"
# )
#
# result2 = connection.execute(ins2)
#
# list3 = [
#     {
#     "cookie_id" : "3",
#     "cookie_name" : "chocolate chip3",
#     "cookie_url" : "http://some.aweso.me/cookie/recipe.html",
#     "quantity" : "3",
#     "unit_cost" :"0.35",
#     },
#     {
#     "cookie_id" : "4",
#     "cookie_name" : "chocolate chip4",
#     "cookie_url" : "http://some.aweso.me/cookie/recipe.html",
#     "quantity" : "4",
#     "unit_cost" :"0.45",
#     },
# ]
#
# ins3 = insert(cookies).values(list3)
# result3 = connection.execute(ins3)
#
# s = select([cookies])
# rp = connection.execute(s)
# result_rp = rp.fetchall()
#
# print(str(result_rp))


